<?php
require_once 'common.php';
require_once 'OaAppModel.php';

/**
 * 审核状态历史日志读老的（OACUST库）模型
 * @author xucongbin
 *
 */
class OaAuditLogHisModel extends OpModel
{
    private $_conn;
    private $_appModel;
    const TB_TMP_AREA_TABLE = "TB_TMP_AREA";
    const TB_TMP_SERVICE_TABLE = "TB_TMP_SERVICE";
    const TB_TMP_GS_TABLE = "TB_TMP_GS";
    //记录建ID 1，新建 2修改  默认1
    const MODIFY_FLAG_ADD = 1;
    const MODIFY_FLAG_UPDATE = 2;
    protected static $_modifyFlag = array(self::MODIFY_FLAG_ADD => '添加', self::MODIFY_FLAG_UPDATE => '修改');
    //审核状态1未审批,2审批中，3审批通过,4已撤销
    const AUDIT_FLAG_WAIT = 1;
    const AUDIT_FLAG_PROCESS = 2;
    const AUDIT_FLAG_PASS = 3;
    const AUDIT_FLAG_CANCEL = 4;
    protected static $_auditFlag = array(self::AUDIT_FLAG_WAIT => '未审批', self::AUDIT_FLAG_PROCESS => '审批中', 
    self::AUDIT_FLAG_PASS => '审批通过', self::AUDIT_FLAG_CANCEL => '已撤销');
    public function __construct ($logger = null)
    {
        if (is_null($logger)) {
            $logger = getOaModelLogger();
        }
        parent::__construct($logger);
        $dbConfig = Oa_Util_Config::getSingleDbConfig(OAII_OACUST_DB_CLUSTER_ID);
//        print_r($dbConfig);
//        $dbConfig = array('user' => 'oacust', 'pass' => 'oacust', 
//        'name' => 'tmptest', 'host' => '10.70.10.7', 'port' => '1521');
        $_conn = oci_connect($dbConfig['user'], $dbConfig['pass'], 
        "{$dbConfig['host']}:{$dbConfig['port']}/{$dbConfig['name']}", 'AL32UTF8');
        if (! $_conn) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $this->_conn = $_conn;
        //$this->_appModel = new OaAppModel(OaAppModel::APP_CONFIG_ENV_PRODUCTION, $logger);
    }
    public function getServiceInfo ($fromDate, $toDate)
    {
        $appIds = array(50524, 50999, 50555);
        $fromDate = date("Y-m-d 00:00:00", strtotime($fromDate));
        $toDate = date("Y-m-d 23:59:59", strtotime($toDate));
        $sql = "SELECT * FROM (
        	SELECT A.*, ROWNUM RN FROM (SELECT SUM(CC) AS DD, SERVICEID FROM (
        		SELECT COUNT(SEQ_ID) AS CC, SERVICEID FROM " . self::TB_TMP_SERVICE_TABLE . "
				WHERE UPDATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss')
        		GROUP BY SERVICEID  UNION ALL  
        		SELECT COUNT(SEQ_ID) AS CC, SERVICEID FROM " . self::TB_TMP_AREA_TABLE . "
				WHERE UPDATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss')
        		GROUP BY SERVICEID UNION ALL  
        		SELECT COUNT(SEQ_ID) AS CC, SERVICEID FROM " . self::TB_TMP_GS_TABLE . "
				WHERE UPDATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss')
        		GROUP BY SERVICEID ) GROUP BY SERVICEID ORDER BY DD DESC
        	) A WHERE ROWNUM <= :end
        ) WHERE RN >= :begin";
        $st = oci_parse($this->_conn, $sql);
        $begin = 0;
        $end = 3;
        $idsStr = join("','", $appIds);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        oci_bind_by_name($st, ':begin', $begin);
        oci_bind_by_name($st, ':end', $end);
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return ($rows);
    }
    /**
     * 获取审核状态记录
     * @param date $fromDate 开始日期
     * @param date $toDate 结束日期
     * @param array $appIds 查询应用ID
     * @param int $offset 
     * @param int $count
     * 
     * @return array
     */
    public function getAuditLog ($fromDate, $toDate, $appIds, $offset, $count)
    {
        $fromDate = date("Y-m-d 00:00:00", strtotime($fromDate));
        $toDate = date("Y-m-d 23:59:59", strtotime($toDate));
        $arr = array();
        $idsStr = join(",", $appIds);
        $tmpArr = $this->getServiceAudit($fromDate, $toDate, $idsStr);
        if ($tmpArr) {
            $arr = $tmpArr;
        }
        $tmpArr = $this->getAreaAudit($fromDate, $toDate, $idsStr);
        if ($tmpArr) {
            $arr = array_merge($arr, $tmpArr);
        }
        $tmpArr = $this->getGSAudit($fromDate, $toDate, $idsStr);
        if ($tmpArr) {
            $arr = array_merge($arr, $tmpArr);
        }
        $result = array_slice($arr, $offset, $count);
        $this->pasrseArr($result);
        return $result;
    }
    /**
     * 重构内容
     * @param array $arr
     * 
     * @return void
     */
    private function pasrseArr (&$arr)
    {
        if (! $arr) {
            return $arr;
        }
        foreach ($arr as $k => $row) {
            $content = '';
            $action = isset(self::$_modifyFlag[$row['MODIFY_FLAG']]) ? self::$_modifyFlag[$row['MODIFY_FLAG']] : '';
            if (isset($row['GROUPID'])) {
                $content = "产品 ID:{$row[SERVICEID]}配置组 ID:{$row[AREAID]}{$action}配置项{$row[GSNAME]}";
            } elseif (isset($row['AREAID'])) {
                $content = "产品 ID:{$row[SERVICEID]}{$action}配置组{$row[NAME]}";
            } else {
                $content = "{$action}产品{$row[SERVICEID]}";
            }
            $arr[$k]['CONTENT'] = $content;
            $arr[$k]['STATUS'] = isset(self::$_auditFlag[$row['AUDIT_FLAG']]) ? self::$_auditFlag[$row['AUDIT_FLAG']] : '';
        }
    }
    /**
     * 获取审核状态记录条数
     * @param date $fromDate 开始日期
     * @param date $toDate 结束日期
     * @param array $appIds 应用ID列表 
     * 
     * @return int
     */
    public function getAuditLogCount ($fromDate, $toDate, $appIds)
    {
        $fromDate = date("Y-m-d 00:00:00", strtotime($fromDate));
        $toDate = date("Y-m-d 23:59:59", strtotime($toDate));
        $inIds = join(",", $appIds);
        $count = 0;
        $sql = "SELECT COUNT(*) FROM " . self::TB_TMP_SERVICE_TABLE . " WHERE TO_CHAR(SERVICEID) IN ({$inIds}) 
        AND CREATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss') 
        UNION ALL 
        SELECT COUNT(*) FROM " . self::TB_TMP_AREA_TABLE . " WHERE TO_CHAR(SERVICEID) IN ({$inIds}) 
        AND CREATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss') 
        UNION ALL 
        SELECT COUNT(*) FROM " . self::TB_TMP_GS_TABLE . " WHERE TO_CHAR(SERVICEID) IN ({$inIds}) 
        AND CREATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss')";
        /*SELECT COUNT(*) FROM ".self::TB_TMP_AREA_TABLE." WHERE appId IN (:inAppArea) 
        AND UPDATETIME BETWEEN TO_DATE(:fromDateArea, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDateArea, 'yyyy-mm-dd hh24:mi:ss') 
        UNION ALL 
        SELECT COUNT(*) FROM ".self::TB_TMP_GS_TABLE." WHERE appId IN (:inAppGS) 
        AND UPDATETIME BETWEEN TO_DATE(:fromDateGS, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDateGS, 'yyyy-mm-dd hh24:mi:ss')";*/
        $st = oci_parse($this->_conn, $sql);
        //oci_bind_by_name($st, ':inApp', $inIds);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        /*oci_bind_by_name($st, ':inAppArea', $inIds);
        oci_bind_by_name($st, ':fromDateArea', $fromDate);
        oci_bind_by_name($st, ':toDateArea', $toDate);
        oci_bind_by_name($st, ':inAppGS', $inIds);
        oci_bind_by_name($st, ':fromDateGS', $fromDate);
        oci_bind_by_name($st, ':toDateGS', $toDate);*/
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_NUM) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        foreach ($rows as $row) {
            $count += (int) $row[0];
        }
        return $count;
    }
    /**
     * 获取产品审核历史日志
     * @param string $fromDate 格式为2010-04-23 00-00-00
     * @param string $toDate
     * @param string $appIds 产品ID字符串格式
     * @return array|boolean
     */
    private function getServiceAudit ($fromDate, $toDate, $appIdStr)
    {
        $sql = "SELECT SVRNAME, AUDIT_FLAG, MODIFY_FLAG, CREATETIME, UPDATETIME FROM " . self::TB_TMP_SERVICE_TABLE . " 
        WHERE TO_CHAR(SERVICEID) IN ({$appIdStr})
        AND CREATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss') 
        ORDER BY CREATETIME DESC";
        $st = oci_parse($this->_conn, $sql);
        //oci_bind_by_name($st, ':inApp', $appIdStr);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return $rows;
    }
    /**
     * 获取区审核历史日志
     * @param string $fromDate 格式为2010-04-23 00-00-00
     * @param string $toDate
     * @param string $appIds 产品ID字符串格式
     * @return array|boolean
     */
    private function getAreaAudit ($fromDate, $toDate, $appIdStr)
    {
        $sql = "SELECT SERVICEID, AREAID, NAME, AUDIT_FLAG, MODIFY_FLAG, CREATETIME, UPDATETIME FROM " . self::TB_TMP_AREA_TABLE .
         " 
        WHERE SERVICEID IN ($appIdStr) 
        AND CREATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss') 
        ORDER BY CREATETIME DESC";
        $st = oci_parse($this->_conn, $sql);
        //oci_bind_by_name($st, ':inApp', $appIdStr);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return $rows;
    }
    /**
     * 获取组审核历史日志
     * @param string $fromDate 格式为2010-04-23
     * @param string $toDate
     * @param string $appIds 产品ID字符串格式
     * @return array|boolean
     */
    private function getGSAudit ($fromDate, $toDate, $appIdStr)
    {
        $sql = "SELECT SERVICEID, AREAID, GROUPID, GSNAME, AUDIT_FLAG, MODIFY_FLAG, CREATETIME, UPDATETIME FROM " . self::TB_TMP_GS_TABLE .
         " 
        WHERE SERVICEID IN ($appIdStr) 
        AND CREATETIME BETWEEN TO_DATE(:fromDate, 'yyyy-mm-dd hh24:mi:ss') 
        AND TO_DATE(:toDate, 'yyyy-mm-dd hh24:mi:ss') 
        ORDER BY CREATETIME DESC";
        $st = oci_parse($this->_conn, $sql);
        //oci_bind_by_name($st, ':inApp', $appIdStr);
        oci_bind_by_name($st, ':fromDate', $fromDate);
        oci_bind_by_name($st, ':toDate', $toDate);
        if (! oci_execute($st)) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $rows = array();
        if (oci_fetch_all($st, $rows, 0, - 1, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC) === false) {
            $e = oci_error($st);
            $this->_logger->log("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        return $rows;
    }
}